## https://www.cnblogs.com/yymn/p/18093787
##
import pymysql

config = {
    "user": "root",
    "password": "123456",
    "host": "192.168.56.102",
    "database":"testdb"
}

connection = pymysql.connect(**config)

# 创建一个游标对象
cursor = connection.cursor()

## 显示数据库列表
cursor.execute("show databases")
result = cursor.fetchall()
print("=========Databases===========")
for row in result:
    print(row)

try:
    cursor.execute("drop database if exits testdb")
except:
    connection.rollback()


## 创建数据库
try:
    cursor.execute("create database if not exists testdb")
    cursor.execute("use testdb")
except:
    connection.rollback()

cursor.execute("show databases")
result = cursor.fetchall()
print("=========Databases===========")
for row in result:
    print(row)

try:
    cursor.execute("drop table if exists employee")
    cursor.execute("create table if not exists employee (\
                   first_name varchar(255) default null comment '姓', \
                   last_name varchar(255) default null comment '名', \
                   age int(11) default null comment '年纪', \
                   sex varchar(255) default null comment '性别', \
                   income varchar(255) default null comment '收入')")
except:
    connection.rollback()

try:
    cursor.execute("insert into employee(first_name, last_name, age, sex, income) \
                values('Li', 'xiaohua', 25, 'M', '5000')")
    connection.commit()
except:
    connection.rollback()

# 执行数据库查询
cursor.execute("select * from employee")
result = cursor.fetchall()
for row in result:
    print(row)

try:
    cursor.execute("insert into employee(first_name, last_name, age, sex, income) \
               values('Wang', 'xiaoming', 25, 'W', '3000')")
    cursor.execute("insert into employee(first_name, last_name, age, sex, income) \
               values('Qiu', 'sheng', 30, 'M', '8000')")
    connection.commit()
except:
    connection.rollback()

# 执行数据库查询
cursor.execute("select age, income from employee where sex='M'")
result = cursor.fetchall()
for row in result:
    print(row)

# 执行更新操作
try:
    cursor.execute("update employee set income = income + 1000")
    connection.commit()
except:
    connection.rollback()

cursor.execute("select income from employee")
result = cursor.fetchall()
for row in result:
    print(row)

## 删除记录
try:
    cursor.execute("delete from employee where income > '5000' and age>29")
    connection.commit()
except:
    connection.rollback()

# 关闭游标和连接
cursor.close()
connection.close()